<?php
/**
 * 数据库操作类
 * 
 * Created		: 2011-06-14
 * Modified		: 2011-07-06 
 * @link		: http://www.itblog.org
 * @copyright	: (C) 2011 ITBlog Inc.
 * @version		: 1.0.0
 * @author		: Joseph Chen (jsph.chen@gmail.com)
 */
class Db extends pdo
{
	
	public static $code = null;
	public static $msg = '';
	public static $debug = true;
	
	private static $connectList = array();
	private static $host = 'localhost';
	private static $name = '';
	private static $user = 'root';
	private static $pass = '';
	private static $charset = 'utf8';
	private static $persistent = false;
	
	protected static $rows = 0;
	protected $priKey = 'id';
	
	protected static $errors = null;

	protected static $dbh = null;
	
	public static $queries = 0;
	public static $stmt = null;
	
	// 控制是否可以结束事务(commit或rollback)
	private static $canEndTransaction = true;
	private static $isStartTransaction = false;// 事务是否已开启
	
	
	
	/**
	 * 连接
	 * @param array $cfg
	 */
	public static function connect($cfg=null, $sign='default')
	{
		if (is_array($cfg)) {
			isset($cfg['host']) && self::$host = $cfg['host'];
			isset($cfg['name']) && self::$name = $cfg['name'];
			isset($cfg['user']) && self::$user = $cfg['user'];
			isset($cfg['pass']) && self::$pass = $cfg['pass'];
			isset($cfg['charset']) && self::$charset = $cfg['charset'];
			isset($cfg['persistent']) && self::$persistent = $cfg['persistent'];
		}
		$dns = 'mysql:dbname='.self::$name.';host='.self::$host;
		$options = array(
			PDO::MYSQL_ATTR_INIT_COMMAND => 'set names \''.self::$charset.'\'',
			PDO::ATTR_PERSISTENT => self::$persistent
		);
		self::$dbh = new PDO( $dns, self::$user, self::$pass, $options);
		self::$connectList[$sign] = self::$dbh;
		//self::setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
	}
	
	/**
	 * 切换使用的DB连接
	 * @param string $sign
	 */
	public static function switchDb($sign)
	{
		if (isset(self::$connectList[$sign])) {
			self::$dbh = self::$connectList[$sign];
			return self::$dbh;
		} else {
			return false;
		}
	}
	
	/**
	 * 设置事务是否可提交状态
	 * Enter description here ...
	 * @param unknown_type $state
	 */
	public static function setTransactionState($state)
	{
		self::$canEndTransaction = $state;
	}
	
	/**
	 * (non-PHPdoc)
	 * @see PDO::beginTransaction()
	 */
	public static function startTransaction()
	{
		if (self::$isStartTransaction) {
			return true;
		} else {
			self::$dbh->beginTransaction();
			self::$isStartTransaction = true;
			// $canEndTransaction不可在这里设置true,必须让业务层,处理这个属性
			return true;
		}
	}
	
	/**
	 * (non-PHPdoc)
	 * @see PDO::commit()
	 */
	public static function commitTransaction()
	{
		if (self::$isStartTransaction) {
			if (self::$canEndTransaction) {
				self::$dbh->commit();
				self::$isStartTransaction = false;
			}
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * (non-PHPdoc)
	 * @see PDO::commit()
	 */
	public static function rollBackTransaction()
	{
		if (self::$isStartTransaction) {
			if (self::$canEndTransaction) {
				self::$dbh->rollBack();
				self::$isStartTransaction = false;
			}
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 执行SQL语句
	 * @param string $sql 要执行的sql语句
	 * @param array | boolean $params 当执行参数化查询时需要的参数
	 * 			当$params设为true时表示执行多条sql语句 
	 * @see PDO::exec()
	 */
	public static function execute($sql, $params=null)
	{
		self::$queries++;
		if (empty($sql) && self::$debug) {
			debug_print_backtrace();
			exit;
		}
		if (is_null($params)) {
			self::$rows = self::$dbh->exec($sql);
			if (self::$rows === false) {
				self::$errors = self::$dbh->errorInfo();
				self::$errors['sql'] = $sql;
				self::$errors['params'] = $params;
				return false;
			} else {
				return true;
			}
		} else {
			self::$stmt = self::$dbh->prepare($sql);
			if (is_array($params)) {
				$param_list = array();
				foreach ($params as $k=>$v) {
					$param_list[$k] = $v;
				}
				$params = $param_list;
				$result = self::$stmt->execute($params);
			} else {
				$result = self::$stmt->execute();
			}
			if (false === $result) {
				self::$errors = self::$stmt->errorInfo();
				self::$errors['sql'] = $sql;
				self::$errors['params'] = $params;
				self::$rows = 0;
			} else {
				self::$errors = null;
				self::$rows = self::$stmt->rowCount();
			}
			return $result;
		}
	}
	
	/**
	 * 返回上次执行操作的受影响记录数
	 */
	public static function affect_rows()
	{
		return self::$rows;
	}
	
	/**
	 * 获取记录数
	 * @param string $table
	 * @param string $where
	 */
	public static function getCounts($table, $where='', $params=null)
	{
		if ($where) {
			$sql = 'select count(*) from '.$table.' where '.$where;
			return (int)self::fetch_one($sql, $params);
		} else {
			$sql = 'select count(*) from '.$table;
			return (int)self::fetch_one($sql, $params);
		}
	}
	
	/**
	 * 获取一个sql查询的所有结果集
	 * @param string $sql
	 * @param array $params
	 * @param int $fetch_style
	 */
	public static function fetches($sql, $params=null, $style=null)
	{
		empty($params) && $params=1;
		empty($style) && $style=PDO::FETCH_ASSOC;
		self::execute($sql, $params);
		// 只有一个字段,则只取第一列数据
		if (self::$stmt->columnCount() == 1) {
			$style = PDO::FETCH_COLUMN;
		}
		$ret = self::$stmt->fetchAll($style);
		if (!$ret) {
			self::$errors = self::$stmt->errorInfo();
			self::$errors['sql'] = $sql;
			self::$errors['params'] = $params;
		} else {
			self::$errors = null;
		}
		// 取完数据要关闭游标,避免表被锁定
		self::$stmt->closeCursor();
		self::$stmt = null;
		return $ret;
	}
	
	/**
	 * 获取查询订中的一条记录
	 * @param string $sql
	 * @param array $params
	 * @param int $fetch_style
	 */
	public static function fetch_one($sql, $params=null, $style=null)
	{
		if (false === strpos($sql, ' limit ') && 0 === strpos($sql, 'select')) {
			$sql .= ' limit 1';
		}
		// 设置$params参数以使exec()方法执行读取结果集的操作
		empty($params) && $params=1;
		empty($style) && $style=PDO::FETCH_ASSOC;
		self::execute($sql, $params);
		$ret = self::$stmt->fetch();
		if (!$ret) {
			self::$errors = self::$stmt->errorInfo();
			self::$errors['sql'] = $sql;
			self::$errors['params'] = $params;
		} else {
			self::$errors = null;
		}
		if (self::$stmt->columnCount() == 1 && !empty($ret)){
			$ret = current($ret);
		}
		// 取完数据要关闭游标,避免表被锁定
		self::$stmt->closeCursor();
		self::$stmt = null;
		return $ret;
	}
	
	/**
	 * 根据指定表名，字段和查询条件获取其中的数据
	 * @param string $table
	 * @param string $field
	 * @param string $where
	 * @return array
	 */
	public static function select($table, $field='*', $where='', $param=null,
			$offset=0, $limit=0, $order='')
	{
		empty($field) && $field = '*';
		empty($where) || $where =" where $where";
		empty($order) || $order =" order by $order";
		$sql = 'select '.$field.' from `'.$table.'`'.$where;
		$sql .= $order;
		if ($limit>0 && $limit>$offset) {
			$sql .= ' limit '.$offset.','.$limit.' ';
		}
		if ($limit == 1) {
			return self::fetch_one($sql, $param);
		} else {
			return self::fetches($sql, $param);
		}
	}
	
	/**
	 * 获取表中指定的字段值的一条记录
	 * @param $table
	 * @param $value 条件查询的值
	 * @param $field 条件查询的字段(默认主键)
	 * @param $fetch_field 要获取的字段列表
	 */
	public static function read($table, $value, $field=null, $fetch_field='*')
	{
		if (is_null($field)) {
			if (!empty(self::$priKey)) {
				$field = self::$priKey;
			} else {
				$field = 'id';
			}
		}
		$key = ':'.$field;
		$sql = 'select '.$fetch_field.' from `'.$table
				.'` where `'.$field.'`='.$key;
		$params = array($key=>$value);
		return self::fetch_one($sql, $params);
	}
	
	/**
	 * 向数据插入一组数据
	 * @param string $table
	 * @param array $data 要更新的数据列表(数组形式
	 * @return int | false
	 */
	public static function insert($table, $data)
	{
		$params = array();
		$fieldList = array();
		$valueList = array();
		// 多维数组,一次插入多条数据
		if (is_array(current($data))) {
			$dataNums = count($data);
			$needFieldList = 1;
			$i = 1;
			foreach ($data as $fa) {
				$tmpValues = array();
				foreach ($fa as $field => $value)
				{
					$needFieldList && $fieldList[] = '`'.$field.'`';
					$params_key = ':'.$field.$i;
					$tmpValues[] = $params_key;
					$params[$params_key] = $value;
				}
				$i++;
				$needFieldList = 0;
				$tmpValues = join(', ', $tmpValues);
				$valueList[] = '('.$tmpValues.')';
			}
			$fieldStr = join(', ', $fieldList);
			$valueStr = join(",\n", $valueList);
			$sql = "INSERT INTO `$table` ($fieldStr) VALUES $valueStr";
		} else {
			$dataNums = 1;
			foreach ($data as $field => $value) {
				$fieldList[] = '`'.$field.'`';
				$params_key = ':'.$field;
				$valueList[] = $params_key;
				$params[$params_key] = $value;
			}
			$fieldStr = join(', ', $fieldList);
			$valueStr = join(', ', $valueList);
			$sql = "INSERT INTO `$table` ($fieldStr) VALUES ($valueStr)";
		}
		$ret = self::execute($sql, $params);
		if (!$ret) {
			self::$rows = 0;
			self::$code = $GLOBALS['LDataBaseInsertFailureCode'];
			self::$msg  = $GLOBALS['LDataBaseInsertFailure'];
		} elseif ($dataNums>1) {
			self::$code = $GLOBALS['LDataBaseInsertSuccessCode'];
			self::$msg  = $GLOBALS['LDataBaseInsertSuccess'];
		} else {
			self::$code = $GLOBALS['LDataBaseInsertSuccessCode'];
			self::$msg  = $GLOBALS['LDataBaseInsertSuccess'];
		}
		return $ret;
	}
	
	/**
	 * 获取上次插入记录的ID
	 * @see PDO::lastInsertId()
	 */
	public static function last_insert_id($name=null)
	{
		return self::$dbh->lastInsertId($name);
	}
	
	/**
	 * 获取自增字段当前值
	 * @param string $table_name
	 */
	public static function get_auto_increment($table_name)
	{
		$sql = 'SHOW TABLE STATUS LIKE \''.$table_name.'\'';
		$record = self::fetch_one($sql);
		return $record['Auto_increment'];
	}
	
	/**
	 * 更新一个表的数据
	 * @param string $table
	 * @param array $data 要更新的数据列表(数组形式)
	 * @param string $where 查询条件字符串
	 * @param string $params SQL查询参数
	 * @param string $retSql 只返回生成的SQL语句,而不是执行
	 * @return int | false
	 */
	public static function update($table, $data, $where='', $params=null, $retSql=0)
	{
		$set_list = array();
		is_null($params) && $params=array();
		foreach ($data as $field => $value) {
			if (false !== strpos($value, '`')) {
				$set_list[] = '`'.$field.'`='.$value;
			} else {
				$params_key = ':'.$field;
				$set_list[] = '`'.$field.'`='.$params_key;
				$params[$params_key] = $value;
			}
		}
		$set = implode(',', $set_list);
		if ($where) {
			$where = ' where ' . $where;
		}
		if (!empty($order)) {
			$order = ' order by '.$order;
		} else {
			$order = '';
		}
		if (!empty($limit)) {
			$limit = ' limit '.(int)$limit;
		} else {
			$limit = '';
		}
		$sql = 'update ' . $table . ' set ' . $set . $where . $order . $limit;
		$ret = self::execute($sql, $params);
		if ($ret === false)
		{
			self::$rows = 0;
			self::$code = $GLOBALS['LDataBaseUpdateFailureCode'];
			self::$msg  = $GLOBALS['LDataBaseUpdateFailure'];
		} else {
			self::$code = $GLOBALS['LDataBaseUpdateSuccessCode'];
			self::$msg  = $GLOBALS['LDataBaseUpdateSuccess'];
		}
		return $ret;
	}
	
	/**
	 * 执行删除操作
	 * @param string $table
	 * @param string $where
	 * @return int | false
	 */
	public static function delete($table, $where, $params=null)
	{
		if ($where) {
			$sql = 'delete from '.$table.' where '.$where;
			$ret = self::execute($sql, $params);
			if ($ret === false) {
				self::$rows = 0;
				self::$code = $GLOBALS['LDataBaseDeleteFailureCode'];
				self::$msg  = $GLOBALS['LDataBaseDeleteFailure'];
			} else {
				self::$code = $GLOBALS['LDataBaseDeleteSuccessCode'];
				self::$msg  = $GLOBALS['LDataBaseDeleteSuccess'];
			}
			return $ret;
		} else {
			$this->errors(
				array(
					'code'	=> $GLOBALS['CDBDeleteWhereCannotEmpty'],
					'info'	=> $GLOBALS['MDBDeleteWhereCannotEmpty']
				)
			);
			return false;
		}
	}

	/**
	 * 获取数据库中所有的表
	 * @return array()
	 */
	public static function show_tables()
	{
		$sql = 'show tables';
		$arr = self::fetches($sql, null, PDO::FETCH_COLUMN);
		$result = array();
		foreach ($arr as $v) {
			$result[] = $v;
		}
		return $result;
	}

	/**
	 * 返回错误信息
	 */
	public static function errors($errors=false)
	{
		if ($errors === false) {
			return self::$errors;
		} else {
			self::$errors = $errors;
			return self::$errors;
		}
	}
}



















